using Rsd.Dudu;
using Rsd.Dudu.BizModel;
using Rsd.Dudu.Core;
using Rsd.Dudu.DBServices;
using Rsd.Dudu.Helpers;
using Rsd.Dudu.Web;
using Rsd.Dudu.Web.Core;
using Rsd.Dudu.Web.MVC;

using Rsd.Faith.Core;
using Rsd.Faith.Models;
using Rsd.Faith.Sys.Core;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Rsd.Faith.Sys.Controllers
{
    /// <summary>
    /// 
    /// </summary>
    public class SystemController : Rsd.Faith.Core.FaithController
    {
        [ApiService("Index",false)]
        public override object Index()
        {
           
            //MyType.InitialTable<Rsd.Faith.Core.ApiTimerModel>(Dudu.DBServices.TableExistOperationType.Alter);
            var list = new List<object>();
            foreach (var g in _taskList.Keys)
            {
                list.Add(new { Group=g,Status= _taskList[g].Status.ToString() });
            }
            return list.ToArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        [ApiService("定时器状态信息", false)]
        public object GetTimerState()
        {
            //select  SUBSTR(`group`, 1,4),count(`group`),complete,success from sys_api_timer group BY SUBSTR(`group`, 1,4),complete,success;
            var report = MyType.GetReportService<Rsd.Faith.Core.ApiTimerModel>();
            var dt = (DataTable)report.Count(obj => obj.Group,"count",false).SelectFormat("SUBSTR({0}, 1,4) as 'group'", obj => obj.Group).GroupByFormat("SUBSTR({0},1,4)", obj => obj.Group).GroupBy(obj => obj.Enabled).GroupBy(obj => obj.Success).Execute();

            var list = new List<object>();
            foreach (var g in _taskList.Keys)
            {
                list.Add(new { group = g, status = _taskList[g].Status.ToString() });
            }
            return new { report=dt.ToJsonList(), state=list.ToArray() };
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pwd"></param>
        /// <returns></returns>
        [PramaRule("pwd", "密码(pwd)", typeof(string))]
        [ApiService("清空账目数据", true)]
        public object Reset(string pwd)
        {
            if (pwd.ToLower() != "admin@redmicro.cn8723")
            {
                return "权限错误";
            }
            var list = new List<string>();
            for (var year = 2000; year < DateTime.Now.Year; year++)
            {
                list.Add("delete from report__fen_hu_zhang_mu"+year+";");
            }
            list.Add("delete from report__fen_hu_zhang_mu" + DateTime.Now.Year + ";");

            list.Add("delete from report__dan_yuan_zhang_mu;");
            list.Add("delete from report__lou_dong_zhang_mu;");
            list.Add("delete from report__xiao_qu_zhang_mu;");
            list.Add("delete from report__yin_hang_zhang_mu;");
            list.Add("delete from report__zeng_zhi_zhang_mu;");
            list.Add("delete from report__zi_jin_cui_jiao_dan;");
            list.Add("delete from report__zi_jin_zong_zhang;");

            list.Add("delete from book__dan_yuan_ming_xi_zhang;");
            list.Add("delete from book__fen_hu_zhang;");
            list.Add("delete from book__fen_hu_zhang_liu_shui;");
            list.Add("delete from book__lou_dong_ming_xi_zhang;");
            list.Add("delete from book__xiao_qu_zong_zhang;");

            var dbSession = (IDBSession)this.GetService(typeof(IDBSession), null);

            return dbSession.DbService.Execute(list.ToArray());
        }

        /// <summary>
        /// 添加定时任务并启动
        /// </summary>
        /// <returns></returns> 
        [PramaRule("list", "定时任务列表(list)", typeof(JsonObject[]))]
        [ApiService("添加定时任务并启动", true)]
        public object NewTimer([Service] IApiTimerService apiTimerService, JsonObject[] list)
        {
            var timerList = new List<ApiTimerModel>();
            foreach (var item in list)
            {
                var group = item.Get<string>("group");
                var path = item.Get<string>("path");
                var text = item.Get<string>("text");

                var timer = new ApiTimerModel(Dudu.BizModel.ObjectStateType.New)
                {
                    Enabled = false,
                    Group = group,
                    Text = text,
                    Path = path
                };
                timerList.Add(timer);
            }

            this.SaveObjects(timerList.ToArray());

            var _host = this.GetConfigAppSetting("timerHost");

            new Task(() =>
            {
                apiTimerService.StartTimer(_host, timerList.ToArray());

            }).Start();


            return timerList.ToArray();
        }


        static Dictionary<string, Task> _taskList = new Dictionary<string, Task>();
        static bool _isBusying = false;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="group"></param>
        /// <returns></returns>
        [PramaRule("group", "分组(group)", typeof(string))]
        [PramaRule("count", "一次处理Api数量(count)", 1.00, 500.00, false, 200)]
        [ApiService("启动定时器", false)]
        public object StartTimer([Service] IApiTimerService apiTimerService, string group, int count)
        {
            if (_isBusying)
            {
                return new ErrorResult<ErrorCodesType>(ErrorCodesType.FS0003);
            }
            this.Context.Server.ScriptTimeout = 3600;

            Task _task = null;

            if (_taskList.ContainsKey(group))
            {
                _task = _taskList[group];
            }
            if (_task != null && _task.Status == TaskStatus.Running)
            {
                return 0;
            }
            if (_task != null && (_task.Status == TaskStatus.RanToCompletion || _task.Status == TaskStatus.Faulted || _task.Status == TaskStatus.Canceled))
            {
                _taskList.Remove(group);
                _task = null;
            }


            var _host = this.GetConfigAppSetting("timerHost");

            if (_task == null)
            {
                _task = new Task(() => {

                    var list = MyType.GetObjects<ApiTimerModel, string>(obj => obj.Group.StartsWith(group) && obj.Enabled == false, obj => obj.Text, SortType.ASC, count, 0);
                    _isBusying = true;

                    while (list.Length > 0)
                    {
                        apiTimerService.StartTimer(_host, list);
                        list = MyType.GetObjects<ApiTimerModel, string>(obj => obj.Group.StartsWith(group) && obj.Enabled == false, obj => obj.Text, SortType.ASC, count, 0);
                    }
                    
                    _isBusying = false;
                    _taskList.Remove(group);
                });

                _taskList.Add(group, _task);
            }

            _task.Start();

            return MyType.GetDBOperator<ApiTimerModel>().Count().Where(obj => obj.Group.StartsWith(group) && obj.Enabled == false).Execute<int>();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="group"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        [PramaRule("group", "分组(group)",4,8,true)]
        [PramaRule("pageIndex", "页序号(pageIndex)",0.00,10000.00,false,0)]
        [PramaRule("pageSize", "页大小(pageSize)", 1.00,500.00,false,100)]
        [ApiService("获取Api列表", false)]
        public object GetApiList(string group,int pageIndex,int pageSize)
        {
            return this.GetPaggingJson<ApiTimerModel,string>(obj=>obj.Group.StartsWith(group),obj=>obj.Text,pageIndex,pageSize,SortType.DESC);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        [ApiService("数据调整", true)]
        public object SqLDataAdjust()
        {
            var list = new List<string>();
            //--调整  缴存 所属期
            list.Add("update fiscal__piao_ju_xin_xi_biao set suo_shu_qi = SUBSTR(jiao_na_ri_qi, 1, 4);");
            //--调整  缴存 状态
            list.Add("update fiscal__piao_ju_xin_xi_biao  set zhuang_tai = 'DJ'  where zhuang_tai = '已登记';");

            list.Add("update fiscal__piao_ju_xin_xi_biao  set zhuang_tai = 'SH'  where zhuang_tai = '已审核';");

            list.Add("update fiscal__piao_ju_xin_xi_biao  set zhuang_tai = 'RZ'  where zhuang_tai = '已入账'or zhuang_tai = '已入帐';");

            // 调整房屋性质 类型
            list.Add("update fiscal__piao_ju_xin_xi_biao set fang_wu_lei_xing = 'DC' where fang_wu_lei_xing = '多层';"); 
            list.Add("update fiscal__piao_ju_xin_xi_biao set fang_wu_lei_xing = 'GC' where fang_wu_lei_xing = '高层';");
            list.Add("update fiscal__piao_ju_xin_xi_biao set fang_wu_xing_zhi = 'SY', fang_wu_lei_xing = 'DC' where fang_wu_lei_xing = '营业性用房';");
            list.Add("update fiscal__piao_ju_xin_xi_biao set fang_wu_xing_zhi = 'SP' where fang_wu_xing_zhi is null;");

            // 调整房屋性质 类型
            list.Add("update info__fang_wu_xin_xi_biao set fang_wu_lei_xing = 'DC' where fang_wu_lei_xing = '多层';");
            list.Add("update info__fang_wu_xin_xi_biao set fang_wu_lei_xing = 'GC' where fang_wu_lei_xing = '高层';");
            list.Add("update info__fang_wu_xin_xi_biao set fang_wu_xing_zhi = 'SY', fang_wu_lei_xing = 'DC' where fang_wu_lei_xing = '营业性用房';");
            list.Add("update info__fang_wu_xin_xi_biao set fang_wu_xing_zhi = 'SP' where fang_wu_xing_zhi is null;");
            // -- 调整收费标准
            list.Add("UPDATE fiscal__piao_ju_xin_xi_biao set shou_fei_biao_zhun = 2 where shou_fei_biao_zhun =0.02;");

            //--调整 房屋信息表 房屋代码
            list.Add(@"select 
CONCAT('update info__fang_wu_xin_xi_biao set remark=''',bian_hao,''', bian_hao=''',CONCAT(xiao_qu_bian_hao,'-',lou_dong_hao,'-',dan_yuan_hao,'-',shi_hao),''' where bian_hao=''',bian_hao,''';')
from (
select bian_hao, xiao_qu_bian_hao, 
case when lou_dong_hao = '/' then '0000'
when lou_dong_hao = '别墅' then '9999'
when lou_dong_hao is null then '0000'
else lou_dong_hao end  as 'lou_dong_hao',
case when dan_yuan_hao = '一' then '01'
when dan_yuan_hao = '二' then '02'
when dan_yuan_hao = '三' then '03'
when dan_yuan_hao = '四' then '04'
when dan_yuan_hao = '五' then '05'
when dan_yuan_hao = '六' then '06'
when dan_yuan_hao = '七' then '07'
when dan_yuan_hao = '八' then '08'
when dan_yuan_hao = '九' then '09'
when dan_yuan_hao = '零' then '00' 
when dan_yuan_hao = '别墅' then '99'
when dan_yuan_hao = '其他' then '00'
when dan_yuan_hao is null then '00'
else dan_yuan_hao end as 'dan_yuan_hao', 
SUBSTR(CONCAT('0000',shi_hao),LENGTH(CONCAT('0000',shi_hao))-3) as 'shi_hao' from info__fang_wu_xin_xi_biao ) A
");
            // --调整房屋代码特殊字符
            list.Add("update info__fang_wu_xin_xi_biao set `bian_hao` = REPLACE(`bian_hao`, '\\', '')  where `bian_hao` like '%\\\\%' and INSTR( `bian_hao`,'\\') = LENGTH(`bian_hao`) - LENGTH('\\') + 1;");
            list.Add(" update info__fang_wu_xin_xi_biao set `bian_hao` = REPLACE(`bian_hao`, '\\', '|') where  `bian_hao` like '%\\\\%';");
            // --
            list.Add("update book__fen_hu_zhang set `fang_wu_bian_hao` = REPLACE(`fang_wu_bian_hao`, '\\', '')  where `fang_wu_bian_hao` like '%\\\\%' and INSTR( `fang_wu_bian_hao`,'\\') = LENGTH(`fang_wu_bian_hao`) - LENGTH('\\') + 1;");
            list.Add("update book__fen_hu_zhang set `fang_wu_bian_hao` = REPLACE(`fang_wu_bian_hao`, '\\', '|') where  `fang_wu_bian_hao` like '%\\\\%';");
            //--
            list.Add("update biz__zi_jin_shi_yong_fen_tan_biao set `fang_wu_bian_hao` = REPLACE(`fang_wu_bian_hao`, '\\', '')  where `fang_wu_bian_hao` like '%\\\\%' and INSTR( `fang_wu_bian_hao`,'\\') = LENGTH(`fang_wu_bian_hao`) - LENGTH('\\') + 1;");
            list.Add("update biz__zi_jin_shi_yong_fen_tan_biao set `fang_wu_bian_hao` = REPLACE(`fang_wu_bian_hao`, '\\', '|') where  `fang_wu_bian_hao` like '%\\\\%';");
            //-- 
            list.Add("update fiscal__piao_ju_xin_xi_biao set `fang_wu_dai_ma` = REPLACE(`fang_wu_dai_ma`, '\\', '')  where `fang_wu_dai_ma` like '%\\\\%' and INSTR( `fang_wu_dai_ma`,'\\') = LENGTH(`fang_wu_dai_ma`) - LENGTH('\\') + 1;");
            list.Add("update fiscal__piao_ju_xin_xi_biao set `fang_wu_dai_ma` = REPLACE(`fang_wu_dai_ma`, '\\', '|') where  `fang_wu_dai_ma` like '%\\\\%';");

            //--调整 缴存 房屋代码
            list.Add("update fiscal__piao_ju_xin_xi_biao a,info__fang_wu_xin_xi_biao b set a.fang_wu_dai_ma = b.bian_hao where a.xiao_qu_bian_hao is null and a.fang_wu_dai_ma = b.remark;");

            //--调整 缴存 小区代码 
            list.Add("update fiscal__piao_ju_xin_xi_biao a,info__fang_wu_xin_xi_biao b set a.xiao_qu_bian_hao = b.xiao_qu_bian_hao where a.xiao_qu_bian_hao is null and a.fang_wu_dai_ma = b.bian_hao;");


            //-- 调整 使用 申请所属期
            list.Add("update biz__zi_jin_shi_yong_shen_qing_biao  set suo_shu_qi = SUBSTR(shen_qing_ri_qi, 1, 4);");
            list.Add("update biz__zi_jin_shi_yong_shen_qing_biao  set zhuang_tai = 'DJ'  where zhuang_tai = '未划拨';");
            list.Add("update biz__zi_jin_shi_yong_shen_qing_biao  set zhuang_tai = 'DJ'  where zhuang_tai = '已登记';");
            list.Add("update biz__zi_jin_shi_yong_shen_qing_biao  set zhuang_tai = 'TG'  where zhuang_tai = '已审核';");
            list.Add("update biz__zi_jin_shi_yong_shen_qing_biao  set zhuang_tai = 'HB'  where zhuang_tai = '已划拨';");
            list.Add("update biz__zi_jin_shi_yong_shen_qing_biao  set bo_fu_fang_shi = 'ZZ'  where bo_fu_fang_shi = '转账';");
            list.Add("update biz__zi_jin_shi_yong_shen_qing_biao  set bo_fu_fang_shi = 'XJ'  where bo_fu_fang_shi = '现金';");
            //  
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao  set fu_kuan_zhang_hu = 'BJ';"); 
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao  set zhuang_tai = 'DJ'  where zhuang_tai = '未分摊';");
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao  set zhuang_tai = 'FT'  where zhuang_tai = '已分摊';");
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao  set zhuang_tai = 'RZ'  where zhuang_tai = '已入账' or zhuang_tai = '已入帐';");
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao  set hua_bo_fang_shi = 'ZZ'  where hua_bo_fang_shi = '转账';");
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao  set hua_bo_fang_shi = 'XJ'  where hua_bo_fang_shi = '现金';");
            //  

            //--调整 划拨表 小区编号 楼栋号 所属期
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao a,biz__zi_jin_shi_yong_shen_qing_biao b set a.shen_qing_dan_hao = b.shen_qing_dan_hao ,a.xiao_qu_bian_hao = b.xiao_qu_bian_hao ,a.lou_dong_hao = b.lou_dong_hao,a.suo_shu_qi = b.suo_shu_qi where a.hua_bo_bian_hao = b.shen_qing_dan_hao;");

            //--调整 分摊表 划拨日期
            list.Add("update biz__zi_jin_shi_yong_fen_tan_biao  A ,biz__zi_jin_shi_yong_hua_bo_biao B set A.hua_bo_ri_qi = B.hua_bo_ri_qi where A.hua_bo_bian_hao = B.hua_bo_bian_hao;");

            //--调整 分摊表 小区编号 楼栋号 所属期
            list.Add("update biz__zi_jin_shi_yong_fen_tan_biao a,biz__zi_jin_shi_yong_shen_qing_biao b set a.xiao_qu_bian_hao = b.xiao_qu_bian_hao ,a.lou_dong_hao = b.lou_dong_hao,a.suo_shu_qi = b.suo_shu_qi where a.shen_qing_dan_hao = b.shen_qing_dan_hao;");

            //--调整 分摊表 状态
            list.Add("update biz__zi_jin_shi_yong_fen_tan_biao set zhuang_tai = 'DJ';");

            //--调整 分摊表 房屋代码
            list.Add("update biz__zi_jin_shi_yong_fen_tan_biao a ,info__fang_wu_xin_xi_biao b set a.fang_wu_bian_hao = b.bian_hao where a.fang_wu_bian_hao = b.remark;");

            //--调整 分摊表 建筑面积
            list.Add("update biz__zi_jin_shi_yong_fen_tan_biao a ,info__fang_wu_xin_xi_biao b set a.jian_zhu_mian_ji = b.jian_zhu_mian_ji where a.fang_wu_bian_hao = b.bian_hao;");
            
            //--调整 房屋信息表 小区代码
            list.Add("update info__fang_wu_xin_xi_biao a,info__xiao_qu__wu_ye_biao b set a.xiao_qu_bian_hao = b.bian_hao where a.xiao_qu_bian_hao is null and a.bei_yong_di_zhi = b.ming_cheng;");
           
            //--调整小区表 开发商名称
            list.Add("UPDATE info__xiao_qu__wu_ye_biao a, info__kai_fa_shang_xin_xi_biao b set a.kai_fa_shang_ming_cheng = b.ming_cheng where a.kai_fa_shang_bian_hao = b.bian_ma;");

            //--调整 划拨表 楼栋号 
            //list.Add("select lou_dong_hao, SUBSTR(lou_dong_hao, instr(lou_dong_hao, '号楼') - 2, 2)+0  from biz__zi_jin_shi_yong_hua_bo_biao where LENGTH(lou_dong_hao) > 3 and instr(lou_dong_hao,'号楼') > 0 and(SUBSTR(lou_dong_hao, instr(lou_dong_hao, '号楼') - 2, 2) + 0) > 9;");

            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao set lou_dong_hao = SUBSTR(lou_dong_hao, instr(lou_dong_hao, '幢') - 3, 3) + 0 where LENGTH(lou_dong_hao) > 3 and instr(lou_dong_hao,'幢') > 0 and(SUBSTR(lou_dong_hao, instr(lou_dong_hao, '幢') - 3, 3) + 0) > 99;");
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao set lou_dong_hao = SUBSTR(lou_dong_hao, instr(lou_dong_hao, '幢') - 2, 2) + 0 where LENGTH(lou_dong_hao) > 3 and instr(lou_dong_hao,'幢') > 0 and(SUBSTR(lou_dong_hao, instr(lou_dong_hao, '幢') - 2, 2) + 0) > 9;");
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao set lou_dong_hao = SUBSTR(lou_dong_hao, instr(lou_dong_hao, '幢') - 1, 1) + 0 where LENGTH(lou_dong_hao) > 3 and instr(lou_dong_hao,'幢') > 0 and(SUBSTR(lou_dong_hao, instr(lou_dong_hao, '幢') - 1, 1) + 0) > 0;");
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao set lou_dong_hao = SUBSTR(lou_dong_hao, instr(lou_dong_hao, '栋') - 3, 3) + 0 where LENGTH(lou_dong_hao) > 10 and instr(lou_dong_hao,'栋') > 0 and(SUBSTR(lou_dong_hao, instr(lou_dong_hao, '栋') - 3, 3) + 0) > 99;");
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao set lou_dong_hao = SUBSTR(lou_dong_hao, instr(lou_dong_hao, '栋') - 2, 2) + 0 where LENGTH(lou_dong_hao) > 10 and instr(lou_dong_hao,'栋') > 0 and(SUBSTR(lou_dong_hao, instr(lou_dong_hao, '栋') - 2, 2) + 0) > 9;");
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao set lou_dong_hao = SUBSTR(lou_dong_hao, instr(lou_dong_hao, '栋') - 1, 1) + 0 where LENGTH(lou_dong_hao) > 10 and instr(lou_dong_hao,'栋') > 0 and(SUBSTR(lou_dong_hao, instr(lou_dong_hao, '栋') - 1, 1) + 0) > 0;");
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao set lou_dong_hao = SUBSTR(lou_dong_hao, instr(lou_dong_hao, '号楼') - 3, 3) + 0 where LENGTH(lou_dong_hao) > 10 and instr(lou_dong_hao,'号楼') > 0 and(SUBSTR(lou_dong_hao, instr(lou_dong_hao, '号楼') - 3, 3) + 0) > 99;");
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao set lou_dong_hao = SUBSTR(lou_dong_hao, instr(lou_dong_hao, '号楼') - 2, 2) + 0 where LENGTH(lou_dong_hao) > 10 and instr(lou_dong_hao,'号楼') > 0 and (SUBSTR(lou_dong_hao ,instr(lou_dong_hao,'号楼')-2,2)+0)>9;");
            list.Add("update biz__zi_jin_shi_yong_hua_bo_biao set lou_dong_hao = SUBSTR(lou_dong_hao, instr(lou_dong_hao, '号楼') - 1, 1) + 0 where LENGTH(lou_dong_hao) > 10 and instr(lou_dong_hao,'号楼') > 0 and(SUBSTR(lou_dong_hao, instr(lou_dong_hao, '号楼') - 1, 1) + 0) > 0;");

            return list.ToArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        [ApiService("数据核查", true)]
        public object SqLDataCheck()
        {
            var list = new List<string>();
            //--查找 房屋代码重复
            list.Add("SELECT bian_hao from( SELECT bian_hao, count(bian_hao) as 'shu_liang' from info__fang_wu_xin_xi_biao GROUP BY bian_hao) a where shu_liang > 1;");

            //17009202 梅仁军（左菊芳）	   1700    120   600 2004 - 02 - 19
            //17002101 左朝启              1700    105   550 2004 - 02 - 18

            //-- 核实 房屋信息 不合规数据(小区编号为空)
            list.Add("select* from info__fang_wu_xin_xi_biao where xiao_qu_bian_hao is null;");

            //-- 检查房屋信息包含特殊字符  
            list.Add("select CONCAT('房屋(',bian_hao,')编码包含非法字符')  from info__fang_wu_xin_xi_biao where  `bian_hao` like '%\\\\%';");

            //--查找 发票号重复 （很多） 重复问题 影响 入账
            list.Add("SELECT fa_piao_bian_hao,shu_liang from(SELECT fa_piao_bian_hao, count(fa_piao_bian_hao) as 'shu_liang' from fiscal__piao_ju_xin_xi_biao GROUP BY fa_piao_bian_hao ) a where shu_liang > 1;");


            //-- 核实 票据表 不合规数据(小区编号为空)
            list.Add("select* from fiscal__piao_ju_xin_xi_biao where xiao_qu_bian_hao is null;");


            //--查找 已入账发票号重复 （很多） 重复问题 影响 入账
            list.Add("SELECT fa_piao_bian_hao,shu_liang from(SELECT fa_piao_bian_hao, count(fa_piao_bian_hao) as 'shu_liang' from fiscal__piao_ju_xin_xi_biao where zhuang_tai = 'RZ' GROUP BY fa_piao_bian_hao ) a where shu_liang > 1;");

            return list.ToArray();

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        [ApiService("基础数据（银行利率）写入", true)]
        public object SqLInsertRates()
        {

            var list = new List<string>();
            //银行利率
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '1990-04-15 01:00:00', 2.88, 6.3, 7.74, 10.08, 10.98, 11.88, 13.68);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '1990-08-21', 2.16, 4.32, 6.48, 8.64, 9.36, 10.08, 11.52);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '1991-04-21', 1.8, 3.24, 5.4, 7.56, 7.92, 8.28, 9);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '1993-05-15', 2.16, 4.86, 7.2, 9.18, 9.9, 10.8, 12.06);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '1993-07-11', 3.15, 6.66, 9, 10.98, 11.7, 12.24, 13.86);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '1996-05-01', 2.97, 4.86, 7.2, 9.18, 9.9, 10.8, 12.06);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '1996-08-23', 1.98, 3.33, 5.4, 7.47, 7.92, 8.28, 9);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '1997-10-23', 1.71, 2.88, 4.14, 5.67, 5.94, 6.21, 6.66);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '1998-03-25', 1.71, 2.88, 4.14, 5.22, 5.58, 6.21, 6.66);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '1998-07-01', 1.44, 2.79, 3.96, 4.77, 4.86, 4.95, 5.22);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '1998-12-07', 1.44, 2.79, 3.33, 3.78, 3.96, 4.14, 4.5);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '1999-06-10', 0.99, 1.98, 2.16, 2.25, 2.43, 2.7, 2.88);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '2002-02-21', 0.72, 1.71, 1.89, 1.98, 2.25, 2.52, 2.79);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '2004-10-29', 0.72, 1.71, 2.07, 2.25, 2.7, 3.24, 3.6);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '2006-08-19', 0.72, 1.8, 2.25, 2.52, 3.06, 3.69, 4.14);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values(UUID(), '2007-03-18', 0.72, 1.98, 2.43, 2.79, 3.33, 3.96, 4.41);");
            list.Add("insert into bank__li_lv_biao(id, tiao_zheng_shi_jian, huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2007-05-19',0.72,2.07,2.61,3.06,3.69,4.41,4.95);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2007-07-21',0.81,2.34,2.88,3.33,3.96,4.68,5.22);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2007-08-22',0.81,2.61,3.15,3.6,4.23,4.95,5.49);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2007-09-15',0.81,2.88,3.42,3.87,4.5,5.22,5.76);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2007-12-21',0.72,3.33,3.78,4.14,4.68,5.4,5.85);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2008-10-09',0.72,3.15,3.51,3.87,4.41,5.13,5.58);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2008-10-30',0.72,2.88,3.24,3.6,4.14,4.77,5.13);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2008-11-27',0.36,1.98,2.25,2.52,3.06,3.6,3.87);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2008-12-23',0.36,1.71,1.98,2.25,2.79,3.33,3.6);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2010-10-20',0.36,1.91,2.2,2.5,3.25,3.85,4.2);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2010-12-26',0.36,2.25,2.5,2.75,3.55,4.15,4.55);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2011-02-09',0.4,2.6,2.8,3,3.9,4.5,5);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2011-04-06',0.5,2.85,3.05,3.25,4.15,4.75,5.25);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2011-07-07',0.5,3.1,3.3,3.5,4.4,5,5.5);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2012-06-08',0.4,2.85,3.05,3.25,4.1,4.65,5.1);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2012-07-06',0.35,2.6,2.8,3,3.75,4.25,4.75);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2014-11-22',0.35,2.35,2.55,2.75,3.35,4,0);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2015-03-01',0.35,2.1,2.3,2.5,3.1,3.75,0);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2015-05-11',0.35,1.85,2.05,2.25,2.85,3.5,0);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2015-06-28',0.35,1.6,1.8,2,2.6,3.25,0);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2015-08-26',0.35,1.35,1.55,1.75,2.35,3,0);");
            list.Add("insert into bank__li_lv_biao (id,tiao_zheng_shi_jian,huo_qi_li_lv,`ding_qi_li_lv_3m` ,`ding_qi_li_lv_6m` ,`ding_qi_li_lv_1y` ,`ding_qi_li_lv_2y` ,`ding_qi_li_lv_3y` ,`ding_qi_li_lv_5y`) values (UUID(),'2015-10-24',0.35,1.1,1.3,1.5,2.1,2.75,0);");



            return list.ToArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        [ApiService("基础数据（银行账户）写入", true)]
        public object SqlInsertBanks()
        {
            var list = new List<string>();
            list.Add("INSERT INTO `bank__kai_hu_biao`   (`id`,`kai_hu_zhang_hao` , `zhang_hu_ming_cheng` , `yin_hang_ming_cheng`,`remark`) VALUES(UUID(), '0000000000000', 'XX市物业管理维修基金', '邮政储蓄银行', '063');"); 
            return list.ToArray();
        }

    }
}
